package soar.test.db.mysql;

import java.util.Random;
import java.util.Date;
import java.text.SimpleDateFormat;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.SQLIntegrityConstraintViolationException;

public class App
{
    public static void main(String args[])
    {
        new App().run();
    }

    private void run()
    {
        Connection conn;

        // connect
        try
        {
            String url="jdbc:mysql://localhost:3306/data";
            conn=DriverManager.getConnection(url, "dbuser", "dbuser");
            conn.setAutoCommit(true);
            Statement stmt=conn.createStatement();
            stmt.executeUpdate("set names utf8");
            stmt.close();
        }
        catch(Exception e)
        {
            System.err.println(String.format(
                        "connect fail: %s %s", e, e.getMessage()));
            return;
        }

        try
        {
            String split="----------------------------------------";
            System.out.println(split);

            add(conn);
            System.out.println(split);

            query(conn);
            System.out.println(split);
        }
        catch(Exception e)
        {
            System.err.println(String.format(
                        "%s %s", e, e.getMessage()));
        }
    }

    private void add(Connection conn) throws Exception
    {
        final String __METHOD__="add";

        try
        {
            String sql="insert into test_user"+
                " (id,update_ts,login,name,job,address)"+
                " values(0,?,?,?,?,?)";
            PreparedStatement stmt=conn.prepareStatement(sql);
            int col=0;
            stmt.setTimestamp(++col, new java.sql.Timestamp(
                        System.currentTimeMillis()));
            stmt.setString(++col, "arron");
            stmt.setString(++col, "东风");
            stmt.setInt(++col, 1);
            stmt.setString(++col, "上海");
            stmt.executeUpdate();
            stmt.close();
            System.out.println("insert succeed");
        }
        catch(SQLIntegrityConstraintViolationException e)
        {
            System.out.println(String.format(
                        "%s: insert duplicate: %s",
                        __METHOD__, e));
        }
    }

    private void query(Connection conn) throws Exception
    {
        String sql="select id,update_ts,name from test_user where id<?";
        PreparedStatement stmt=conn.prepareStatement(sql);
        int col=0;
        stmt.setInt(++col, 100);
        ResultSet rs=stmt.executeQuery();
        while(rs.next())
        {
            col=0;
            long id=rs.getLong(++col);
            Date update_ts=rs.getTimestamp(++col);
            String name=rs.getString(++col).trim();
            System.out.println(String.format(
                        "%d %s %s", id,
                        update_ts,
                        name));
        }
        rs.close();
        stmt.close();
    }
}
